﻿create database Student

on
(
	name='Student',
	filename='D:\test\Student.mdf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)

log on
(
	name='Student_log',
	filename='D:\test\Student_log.ldf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Student

go
create table Class
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(20) unique not null,
)

create table Student
(
	StudentID int primary key identity(1,1),
	ClassID int references Class(ClassID) not null,
	StudentName nvarchar(20) ,
	StudentSex nvarchar(1) check(StudentSex='' or StudentSex='Ů'),
	StudentBirthday date,
	StudentPhone nvarchar(11) unique,
	StudentAddess nvarchar(200),
)
create table Course
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int default('1') check(CourseCredit='1' or CourseCredit='2' or CourseCredit='3' or CourseCredit='4' or CourseCredit='5') not null,
)

create table Score
(
	ScoreID int primary key identity(1,1),
	StudentID int references Student(StudentID) ,
	CourseID int references Course(CourseID),
	Score decimal(5,2) unique not null,
)